﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class SqlEmplois_classe
    {
        private static Emplois_classe ReaderToObject(SqlDataReader dr)
        {
            Emplois_classe _Emplois_classe = new Emplois_classe();
            _Emplois_classe.Id = int.Parse(dr["Id"].ToString());
            _Emplois_classe.Annee_scolaire = dr["Annee_scolaire"].ToString();
            _Emplois_classe.Jour = dr["Jour"].ToString();
            _Emplois_classe.Heure_deb  =DateTime .Parse ( dr["Heure_deb"].ToString());
            _Emplois_classe.Heure_deb_group2   =DateTime .Parse ( dr["Heure_deb_group2"].ToString());
            _Emplois_classe.Heure_fin  =DateTime .Parse ( dr["Heure_fin"].ToString());
            _Emplois_classe.Heure_fin_group2   =DateTime .Parse ( dr["Heure_fin_group2"].ToString());
            _Emplois_classe.Salle  = dr["Salle"].ToString();
            _Emplois_classe.Salle_group2 = dr["Salle_group2"].ToString();


            if (!object.ReferenceEquals(dr["Id_classe"], DBNull.Value))
            {
                _Emplois_classe.classe = Classe.GetClasseById (int.Parse(dr["Id_classe"].ToString()));
            }

            if (!object.ReferenceEquals(dr["Id_matiere"], DBNull.Value))
            {
                _Emplois_classe.matiere  = Matiere.GetMatiereById(int.Parse(dr["Id_matiere"].ToString()));
            }
            return _Emplois_classe;
        }

        public static Emplois_classe GetEmplois_classeById(int id)
        {
            Emplois_classe emplois_classe = new Emplois_classe();
            string strSQL = @"select * from Emplois_classe where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        emplois_classe = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return emplois_classe;
        }

        public static int insertEmplois_classe(Emplois_classe EC)
        {
            int n = 0;
            string strSQL = @"insert into Emplois_classe(Annee_scolaire,Jour,Heure_deb,"+
                "Heure_deb_group2,Heure_fin,Heure_fin_group2,Salle,Salle_group2,Id_matiere,Id_classe)"+
                " values(@Annee_scolaire,@Jour,@Heure_deb,@Heure_deb_group2,@Heure_fin,@Heure_fin_group2,"+
                "@Salle,@Salle_group2,@Id_matiere,@Id_classe)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Annee_scolaire", EC.Annee_scolaire );
                    cmd.Parameters.AddWithValue("@Jour", EC.Jour );
                    cmd.Parameters.AddWithValue("@Heure_deb", EC.Heure_deb  );
                    cmd.Parameters.AddWithValue("@Heure_deb_group2", EC.Heure_deb_group2 );
                    cmd.Parameters.AddWithValue("@Heure_fin", EC.Heure_fin );
                    cmd.Parameters.AddWithValue("@Heure_fin_group2", EC.Heure_fin_group2  );
                    cmd.Parameters.AddWithValue("@Salle", EC.Salle );
                    cmd.Parameters.AddWithValue("@Salle_group2", EC.Salle_group2 );
                    cmd.Parameters.AddWithValue("@Id_classe", EC.classe.Id  );
                    cmd.Parameters.AddWithValue("@Id_matiere", EC.matiere.Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateEmplois_classe(Emplois_classe EC)
        {
            int n = 0;

            string strSQl = @"UPDATE Emplois_classe SET (@Annee_scolaire IS NULL OR Annee_scolaire=@Annee_scolaire) ," +
                "(@Jour IS NULL OR Jour=@Jour),(@Heure_deb IS NULL OR Heure_deb=@Heure_deb),"
                +"(@Heure_deb_group2 IS NULL OR Heure_deb_group2=@Heure_deb_group2),(@Heure_fin IS NULL OR Heure_fin=@Heure_fin),"+
                "(@Heure_fin_group2 IS NULL OR Heure_fin_group2=@Heure_fin_group2),(@Salle IS NULL OR Salle=@Salle),"
                +"(@Salle_group2 IS NULL OR Salle_group2=@Salle_group2), Id_classe=@Id_classe,Id_matiere=@Id_matiere where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);
                   cmd.Parameters.AddWithValue("@Annee_scolaire", EC.Annee_scolaire );
                    cmd.Parameters.AddWithValue("@Jour", EC.Jour );
                    cmd.Parameters.AddWithValue("@Heure_deb", EC.Heure_deb  );
                    cmd.Parameters.AddWithValue("@Heure_deb_group2", EC.Heure_deb_group2 );
                    cmd.Parameters.AddWithValue("@Heure_fin", EC.Heure_fin );
                    cmd.Parameters.AddWithValue("@Heure_fin_group2", EC.Heure_fin_group2  );
                    cmd.Parameters.AddWithValue("@Salle", EC.Salle );
                    cmd.Parameters.AddWithValue("@Salle_group2", EC.Salle_group2 );
                    cmd.Parameters.AddWithValue("@Id_classe", EC.classe.Id  );
                    cmd.Parameters.AddWithValue("@Id_matiere", EC.matiere.Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deleteEmplois_classe(int id)
        {
            int n = 0;
            string strSQL = @"delete from Emplois_classe where id=@id)";

            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);//req pr delete
                    cmd.Parameters.AddWithValue("@id", id);
                    if (cmd.ExecuteNonQuery() > 0)
                        n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }
    }
    }

